﻿using System;
using System.Collections.Generic;
using System.Text;
using SQLServerHealthCheck.Model;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Linq;
using System.Collections;
using System.IO;
using AG.Utilities;


namespace SQLServerHealthCheck.SQLServer
{

    public class CollectPerformanceData
    {
        List<Exception> errors;
        HealthCheckDataContext dc;
        SQLServerInstance sqlServerInstance;

        public List<Exception> Collect(SQLServerInstance SqlServerInstance, bool collectCollectdetails)
        {
            errors = new List<Exception>();
            sqlServerInstance = SqlServerInstance;

            try
            {
                dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());
                // Create If check to select Collect column details with boolean 

                FillPerformanceData();
                FillRelations();
                FillWaitStatsData();

                if (collectCollectdetails)
                {
                    CollectColumnDetails();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            return errors;

        }

        private void FillWaitStatsData()
        {
            try
            {

                if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                {


                    using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                    {
                        cn.Open();


                        String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                        String serverName = parts[0];

                        Server server = dc.Servers.FirstOrDefault(s => s.Name.ToLower() == serverName.ToLower());

                        if (server != null)
                        {

                            Service service = server.Services.FirstOrDefault(s2 => s2.InstanceName.ToLower() == sqlServerInstance.InstanceName.ToLower());

                            if (service != null)
                            {


                                String sql = @"
SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
                            WITH Waits AS
                            (
	                            SELECT
	                            wait_type,
	                            100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
	                            ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
	                            FROM sys.dm_os_wait_stats
	                            WHERE wait_time_ms > 0
	                            AND wait_type NOT LIKE N'%SLEEP%'
	                            AND wait_type NOT LIKE N'%IDLE%'
	                            AND wait_type NOT LIKE N'%QUEUE%'
	                            AND wait_type NOT IN( N'CLR_AUTO_EVENT'
	                            , N'REQUEST_FOR_DEADLOCK_SEARCH'
	                            , N'SQLTRACE_BUFFER_FLUSH'
	                            /* filter out additional irrelevant waits */ )
                            )
                            SELECT
	                            W1.wait_type,
	                            CAST(W1.pct AS NUMERIC(5, 2)) AS pct
                            FROM 
	                            Waits AS W1
		                            JOIN 
	                            Waits AS W2 ON W2.rn <= W1.rn
                            GROUP BY 
	                            W1.rn, 
	                            W1.wait_type, 
	                            W1.pct 
                            HAVING 
	                            SUM(W2.pct) - W1.pct < 80 -- percentage threshold
		                            OR 
	                            W1.rn <= 5
                            ORDER BY 
	                            W1.rn;
COMMIT TRANSACTION;
    ";



                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();



                                while (rd.Read())
                                {

                                    Waitstat dt = new Waitstat();
                                    dt.WaitType = rd[0].ToString();
                                    dt.WaitTimePercentage = rd.GetDecimal(1);

                                    service.Waitstats.Add(dt);
                                }


                                rd.Close();
                                dc.SubmitChanges();
                            }
                        }

                    }
                    
                }



            }
            catch (Exception ex)
            {
                errors.Add(ex);
                Logger.Error(ex);

            }



        }


        public List<Exception> CollectColumnDetails()
        {
            try
            {

                FillColumnData();
                FillIndexColumnData();

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            return errors;



        }


        public void FillPerformanceData()
        {

            try
            {

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];

                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                    if (service != null)
                    {


                        foreach (Database database in service.Databases)
                        {
                            if (
                                database.DatabaseName.Trim() != "master" &&
                                database.DatabaseName.Trim() != "model" &&
                                database.DatabaseName.Trim() != "distribution" &&
                                database.DatabaseName.Trim() != "msdb" &&
                                database.DatabaseName.Trim() != "tempdb" &&
                                database.DatabaseName.Trim() != "distribution" &&
                                !database.DatabaseName.Contains("ReportServer") &&
                                database.State.ToLower() != "offline" &&
                                database.CompatibilityLevel != "80"
                                )
                            {
                                FillTables(database, service);

                                if (System.Convert.ToInt32(database.CompatibilityLevel) > 80)
                                {
                                    FillIndexes(database, service);
                                }

                            }
                        }
                    }

                }

            }
            catch (Exception ex)
            {
                errors.Add(ex);
                Logger.Error(ex);

            }



        }

        private void FillTables(Database database, Service service)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString(database.DatabaseName)))
                {


                    using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString(database.DatabaseName)))
                    {
                        cn.Open();
                        String sql = @"SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
CREATE TABLE #spaceused
                                                (
                                                    Name nvarchar(255),
                                                    Rows int,
                                                    [Total size] varchar(50),
                                                    [Data size] varchar(50),
                                                    [Index size] varchar(50),
                                                    Unused varchar(50)
                                                )
                                                DECLARE @table_name nvarchar(255) 

                                                DECLARE tables_cursor CURSOR LOCAL FORWARD_ONLY FOR
                                                   SELECT   a.name

												+ '.' + Object_Name(id) AS tablename 
													FROM sysobjects s join  sys.tables  b  on s.id=b.object_id
													 join 
													 sys.schemas a   on b.schema_id = a.schema_id
													   WHERE s.type='U' 

                                                OPEN tables_cursor
                                                FETCH NEXT FROM tables_cursor INTO @table_name
                                                WHILE @@FETCH_STATUS = 0
                                                BEGIN
                                                    INSERT INTO #spaceused EXEC sp_spaceused @table_name
                                                    FETCH NEXT FROM tables_cursor INTO @table_name
                                                END

                                                SELECT	Name,
														CAST([Rows] AS int) AS [Row Count],
														CAST(REPLACE( [Total size] , 'KB' , '' ) AS decimal) AS [Total size],
														CAST(REPLACE( [Data size], 'KB' , '' )AS decimal) AS[Data size],
														CAST(REPLACE([Index size],'KB' , '' )AS decimal) AS [Index size],
														CAST(REPLACE([Unused],'KB' , '' ) AS decimal) AS [Unused]
                                                 FROM #spaceused
                                                 
                                                 
                                                 ORDER BY CONVERT(int, LEFT([Total size],
                                                                     CHARINDEX(' KB', [Total size]))) DESC 

                                                DROP TABLE #spaceused
                                                CLOSE tables_cursor
                                                DEALLOCATE tables_cursor;
Commit TRANSACTION;
                                            ";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();



                        while (rd.Read())
                        {


                            Table dt = new Table();
                            dt.TableName = rd.IsDBNull(0) ? null : rd[0].ToString();
                            dt.Rows = rd.IsDBNull(1) ? 0 : rd.GetInt32(1);
                            dt.TotalSize = rd.IsDBNull(2) ? 0 : rd.GetDecimal(2);
                            dt.DataSize = rd.IsDBNull(3) ? 0 : rd.GetDecimal(3);
                            dt.IndexSize = rd.IsDBNull(4) ? 0 : rd.GetDecimal(4);
                            dt.Unused = rd.IsDBNull(5) ? 0 : rd.GetDecimal(5);

                            database.Tables.Add(dt);
                        }


                        rd.Close();


                    }


                    dc.SubmitChanges();

                }


            }


            catch (Exception ex)
            {
                errors.Add( ex);
                Logger.Error(database.DatabaseName, ex);
            }


        }

        private void FillIndexes(Database database, Service service)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                {


                    using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString(database.DatabaseName)))
                    {
                        cn.Open();
                        String sql = @"	
SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
                  Declare @Str Varchar(8000)

                  Create Table ##SQL_FragLIST 
                    (
		                DatabaseName Varchar(8000),
		                ObjectID Varchar(8000),
		                ObjectName Varchar(8000),
		                Index_ID Varchar(8000),
		                IndexName varchar(8000),
		                Index_Type_DESC Varchar(8000),
		                IndexSize decimal(10,2), -- In MB
		                last_updated datetime,
		                Avg_Fragmentation_In_Percent decimal(5,2),
		                Is_PK bit,
						IndexSeeks bigint NULL,
						IndexScans bigint NULL,
						IndexUpdates bigint NULL,
						IndexLookups bigint NULL
                    )

                declare @dbname varchar(8000)
                Set @dbname=Db_Name() 
                Set @Str='

                  SELECT 	
			                DatabaseName,
                            ObjectId,
			                ObjectName,
			                IndexId,
			                I.name AS IndexName,
			                Index_Description,
			                CONVERT(DECIMAL(16,1),sum(page_count * 8)/ 1024)as ''IndexSize'',
			                last_updated,
			                CASE
				                WHEN SUM(ID.page_count) > 0 THEN
					                SUM(Avg_Fragmentation_In_Percent * ID.page_count) /SUM(ID.page_count)
				                ELSE
					                0
			                END AS Avg_Fragmentation_In_Percent ,
			                I.is_primary_key AS Is_PK,
							S.user_seeks AS IndexSeeks,
							S.user_scans AS IndexScans,
							S.user_updates AS IndexUpdates,
							S.user_lookups AS IndexLookups
                  FROM
                  ( 
			                SELECT 
				                Distinct DB_Name(Database_id) as ''DatabaseName'',
				                Object_ID as ''ObjectId'',
				                Object_Name(Object_id) as ''ObjectName'',
				                Index_ID as ''IndexId'',
				                Index_Type_Desc as ''Index_Description'',
				                STATS_DATE(object_id,index_id) as ''last_updated'',
				                Avg_Fragmentation_In_Percent,
				                page_count
			                FROM 
				                sys.dm_db_index_physical_stats (DB_ID('''+@dbname +'''), NULL, NULL, NULL, ''lIMITED'') 
			                WHERE 
				                Object_id is not null 
                    ) ID
			                INNER JOIN
		                sys.indexes I ON ID.IndexId = I.Index_Id AND ID.ObjectId = I.Object_id
							LEFT OUTER JOIN
						sys.dm_db_index_usage_stats S ON 
													S.object_id = I.Object_id
														AND 
													S.index_id = I.Index_Id
														AND
													db_name(S.database_id) = ''' + @dbname + '''
	                GROUP BY
			                DatabaseName,
                            ObjectId,
			                ObjectName,
			                IndexId,
			                I.name ,
			                Index_Description,
			                last_updated,
			                I.is_primary_key,
							S.user_seeks,
							S.user_scans,
							S.user_updates,
							S.user_lookups
                    '


                    Insert into ##SQL_FragLIST Exec (@Str)

                    Select DatabaseName,
                    ObjectName,
                    Index_ID,
                    IndexNAME,
                    Index_Type_DESC,
                    IndexSize,
                    last_updated,
                    Avg_Fragmentation_In_Percent, 
                    Is_PK,
					IndexSeeks,
					IndexScans,
                   	IndexUpdates ,
						IndexLookups 
    
                    FROM ##SQL_FragLIST
    
                    Drop Table ##SQL_FragLIST;
Commit Transaction;
    
    
                                                                               
                                            ";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();

                        // if tables are null go to next database in the reader..
                        while (rd.Read())
                        {
                            Table dt = null;
                            dt = new Table();
                            string databaseName = rd[0].ToString();

                            string tableName = rd[1].ToString();
                            string indexname = rd[3].ToString();

                            database = dc.Databases.FirstOrDefault(db => db.DatabaseName == databaseName);
                            if (database != null)
                            {

                                dt = database.Tables.FirstOrDefault(d => d.TableName == tableName);

                                if (dt != null)
                                {
                                    Index idf = new Index();
                                    idf.IndexName = rd[3].ToString();
                                    idf.IndexType = rd[4].ToString();
                                    idf.IndexSize = rd.GetDecimal(5);
                                    idf.Statistics_LastUpdatedDate = rd.IsDBNull(6) ? (DateTime?)null : rd.GetDateTime(6);
                                    idf.Fragmentation = rd.GetDecimal(7);
                                    idf.IS_PK = rd.GetBoolean(8);
                                    idf.IndexSeeks = rd.IsDBNull(9) ? 0 : rd.GetInt64(9);
                                    idf.IndexScans = rd.IsDBNull(10) ? 0 : rd.GetInt64(10);
                                    idf.IndexUpdates = rd.IsDBNull(11) ? 0 : rd.GetInt64(11);
                                    idf.IndexLookups = rd.IsDBNull(12) ? 0 : rd.GetInt64(12);
                                    dt.Indexes.Add(idf);
                                }

                            }
                        }

                        dc.SubmitChanges();
                        rd.Close();

                    }


                }


            }


            catch (Exception ex)
            {
                Logger.Error(database.DatabaseName, ex);

                errors.Add(ex);

            }



        }






        public void FillColumnData()
        {

            try
            {
                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];

                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                    if (service != null)
                    {



                        FillColumns(service);




                    }


                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);

            }

        }

        private void FillColumns(Service service)
        {
            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                {


                    using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                    {
                        cn.Open();
                        String sql = @"SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
CREATE TABLE #TempColumn
                                                (
                                                DatabaseName nvarchar(255),
                                                TableName VARCHAR(255),                                               
                                                ColumnName VARCHAR(255),
                                                Column_Type VARCHAR(50),
                                                Size DECIMAL(18,0)
                                                )
                                                DECLARE @DatabaseName nvarchar(50)

                                                DECLARE GetDatabaseName CURSOR FOR
                                                SELECT d.name FROM sys.databases d 


                                                OPEN GetDatabaseName

                                                FETCH NEXT FROM GetDatabaseName INTO @DatabaseName

                                                WHILE @@FETCH_STATUS = 0

                                                BEGIN
	
                                                EXECUTE(
                                                '
                                                INSERT #TempColumn
                                                (
                                                DatabaseName ,
                                                TableName ,                                                
                                                ColumnName ,
                                                Size,
                                                Column_Type
                                                )
                                                SELECT 
                                                '''+@DatabaseName+''',
	                                                t.name AS TableName,	                                             
	                                                c.name AS ColumnName, 
	                                                c.max_length AS Size, 
	                                                st.name AS Column_Type  
                                                FROM 
	                                                ['+@DatabaseName+'].sys.tables AS t INNER JOIN	
	                                                ['+@DatabaseName+'].sys.columns AS c ON c.object_id = t.object_id INNER JOIN	                                                                                                
	                                                ['+@DatabaseName+'].sys.types st ON c.user_type_id = st.user_type_id
                                               
                                                ')

                                                FETCH NEXT FROM GetDatabaseName INTO @DatabaseName
                                                END
                                                CLOSE GetDatabaseName
                                                DEALLOCATE GetDatabaseName

                                                select*
                                                from #TempColumn
                                                Order By 
                                                DatabaseName ,
                                                TableName, 
                                                ColumnName
                                                
                                                DROP TABLE #TempColumn;
COMMIT TRANSACTION;";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();


                        Index i = null;

                        Column c = null;
                        Table table = null;
                        Database database = null;

                        String previousDatabaseName = "";
                        String previousTableName = "";

                        while (rd.Read())
                        {

                            c = new Column();
                            string databaseName = rd[0].ToString();

                            if (previousDatabaseName == "" || databaseName != previousDatabaseName)
                            {

                                database = dc.Databases.FirstOrDefault(d => d.DatabaseName == databaseName);
                            }
                            if (database != null)
                            {
                                string tableName = rd[1].ToString();

                                if (previousTableName == "" || tableName != previousTableName)
                                {
                                    table = dc.Tables.FirstOrDefault(dt => dt.TableName == tableName);
                                }


                                if (table != null)
                                {
                                    string columnName = rd[2].ToString();


                                    if (columnName != null)
                                    {

                                        c.Name = columnName;

                                        c.Size = rd.GetDecimal(4);
                                        c.DataType = rd[3].ToString();



                                        table.Columns.Add(c);


                                    }

                                }
                                previousDatabaseName = databaseName;
                                previousTableName = tableName;
                            }

                        }

                        dc.SubmitChanges();
                        rd.Close();

                    }



                }




                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Completed for " + service.InstanceName.ToString());


            }


            catch (Exception ex)
            {



                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " Some error occured in Performance Check.Please check exception file for details");

            }
        }

        public void FillIndexColumnData()
        {

            try
            {
                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];

                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                    if (service != null)
                    {



                        FillIndexColumns(service);


                    }


                }
            }

            catch (Exception ex)
            {

                errors.Add(ex);


            }

        }

        private void FillIndexColumns(Service service)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                {


                    using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                    {
                        cn.Open();
                        String sql = @"CREATE TABLE #TempColumn
                                                (
                                                DatabaseName nvarchar(255),
                                                TableName VARCHAR(255),
                                                IndexName VARCHAR(255),
                                                ColumnName VARCHAR(255),
                                                Column_Type VARCHAR(50),
                                                Size DECIMAL(18,0),
                                                isInclude BIT
                                                )
                                                DECLARE @DatabaseName nvarchar(50)

                                                DECLARE GetDatabaseName CURSOR FOR
                                                SELECT d.name FROM sys.databases d 


                                                OPEN GetDatabaseName

                                                FETCH NEXT FROM GetDatabaseName INTO @DatabaseName

                                                WHILE @@FETCH_STATUS = 0

                                                BEGIN
	
                                                EXECUTE('
                                                INSERT #TempColumn
                                                (
                                                DatabaseName,
                                                TableName ,
                                                IndexName ,
                                                ColumnName ,
                                                Size,
                                                Column_Type,
                                                isInclude
                                                )
                                                SELECT 
                                                '''+@DatabaseName+''',
	                                                t.name AS TableName,
	                                                i.name AS IndexName, 
	                                                c.name AS ColumnName, 
	                                                c.max_length AS Size, 
	                                                st.name AS Column_Type,
	                                                ic.is_included_column AS isInclude
	                                                  
                                                FROM 
	                                                ['+@DatabaseName+'].sys.index_columns AS ic INNER JOIN
	                                                ['+@DatabaseName+'].sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id INNER JOIN
	                                                ['+@DatabaseName+'].sys.tables AS t INNER JOIN
	                                                ['+@DatabaseName+'].sys.indexes AS i ON i.object_id = t.object_id ON ic.index_id = i.index_id AND ic.object_id = i.object_id INNER JOIN
	                                                ['+@DatabaseName+'].sys.types st ON c.user_type_id = st.user_type_id
                                                WHERE     (i.index_id > 0) AND (i.type IN (1, 2)) AND (i.is_primary_key = 0) AND (i.is_unique_constraint = 0) AND (i.is_disabled = 0) AND (i.is_hypothetical = 0) AND 
                                                                (ic.key_ordinal > 0)
                                                ORDER BY ic.key_ordinal')

                                                FETCH NEXT FROM GetDatabaseName INTO @DatabaseName
                                                END
                                                CLOSE GetDatabaseName
                                                DEALLOCATE GetDatabaseName

                                                select*
                                                from #TempColumn
                                                Order By 
                                                [DatabaseName],
                                                TableName ,
                                                IndexName 
                                                
                                                DROP TABLE #TempColumn";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();


                        Index idf = null;

                        Column c = null;
                        Table table = null;
                        IndexColumn ic = null;
                        while (rd.Read())
                        {
                            c = new Column();
                            ic = new IndexColumn();

                            string databaseName = rd[0].ToString();

                            Database database = dc.Databases.FirstOrDefault(d => d.DatabaseName == databaseName);

                            if (database != null)
                            {
                                string tableName = rd[1].ToString();

                                table = database.Tables.FirstOrDefault(dt => dt.TableName == tableName);

                                if (table != null)
                                {
                                    string IName = rd[2].ToString();

                                    idf = table.Indexes.FirstOrDefault(i => i.IndexName == IName);

                                    if (idf != null)
                                    {

                                        string columnName = rd[3].ToString();

                                        c = dc.Columns.FirstOrDefault(col => col.Name == columnName);

                                        if (c != null)
                                        {

                                            ic.Column = c;
                                            ic.IsInclude = Convert.ToBoolean(rd["isInclude"]);
                                            idf.IndexColumns.Add(ic);
                                        }




                                    }


                                }

                            }

                        }
                        dc.SubmitChanges();
                        rd.Close();

                    }



                }

            }


            catch (Exception ex)
            {



                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " Some error occured in Performance Check.Please check exception file for details");

            }


        }
        public void FillRelations()
        {

            try
            {

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name.ToLower() == serverName);

                Logger.Debug("FillDatabasesAndFiles, ServerName=" + serverName + ", Instance=" + sqlServerInstance.InstanceName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName.ToLower() == sqlServerInstance.InstanceName.ToLower());
                    if (service != null)
                    {

                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Started for " + service.InstanceName.ToString());

                            //Check the connection to SQL Server Instance and gets the details.


                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = GetRelations();

                                Logger.Debug("GetRelations=" + sql);

                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();

                                Database db = null;
                                string prevname = "";
                                while (rd.Read())
                                {



                                    Relation rel = new Relation();



                                    if (prevname.ToLower() != rd[0].ToString().ToLower())
                                        db = service.Databases.FirstOrDefault(d => d.DatabaseName.ToLower() == rd[0].ToString().ToLower());

                                    if (db != null)
                                    {
                                        if ((db.DatabaseName.ToLower() != "master") && (db.DatabaseName.ToLower() != "tempdb") && (db.DatabaseName.ToLower() != "model") && (db.DatabaseName.ToLower() != "msdb"))
                                        {
                                            Table FKTab = db.Tables.FirstOrDefault(t => t.TableName.ToLower() == rd[1].ToString().ToLower());
                                            Table PKTab = db.Tables.FirstOrDefault(t => t.TableName.ToLower() == rd[3].ToString().ToLower());


                                            if (FKTab != null && PKTab != null)
                                            {
                                                rel.FK_TableID = FKTab.TableID;
                                                rel.PK_TableID = PKTab.TableID;
                                                rel.FK_Column = rd[2].ToString();

                                                rel.PK_Column = rd[4].ToString();

                                                dc.Relations.InsertOnSubmit(rel);

                                            }
                                        }
                                        prevname = db.DatabaseName;
                                    }
                                }
                                dc.SubmitChanges();
                                rd.Close();
                            }




                        }
                    }
                }



            }

            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);
            }


        }
        private String GetRelations()
        {
            String sql = @" SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
Create table #Foreignkeys(
             DatabaseName varchar(255),
             FK_Table varchar(255),
                                        FK_Column varchar(255),
                                        PK_Table varchar(255),
                                        PK_Column varchar(255),
                                        Constraint_Name varchar(255)
       )
       DECLARE
             @database varchar(255)
            
       DECLARE databases_cursor CURSOR FOR
      
       SELECT
             d.name
       FROM
             sys.databases d
        
       OPEN databases_cursor
 
       FETCH NEXT FROM databases_cursor INTO @database
       DECLARE @query varchar(8000)
    WHILE @@FETCH_STATUS = 0
    BEGIN
             Set @query='USE [' + @database + ']
                    INSERT INTO #Foreignkeys
                                  (     
										DatabaseName,
										FK_Table,
                                        FK_Column,
                                        PK_Table,
                                        PK_Column,
                                        Constraint_Name
                                        
                                  )
                                  SELECT
                                        ''' + @database + ''',
                                        FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = ''PRIMARY KEY''
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
                                  '
      
             exec( @query)
 
        FETCH NEXT FROM databases_cursor INTO @database
       END
    CLOSE databases_cursor
    DEALLOCATE databases_cursor;
 
 
       SELECT * FROM #Foreignkeys
 
 
       drop table #Foreignkeys;
COMMIT TRANSACTION;
	
                   



";
            return sql;
        }
    }
}
